﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySQLDriverCS;

namespace TJADRICD.CollaborativeDesignPlugin
{
    class MySQLAccount
    {
        public string userId;
        public String userName;
    }
    class MySQLAccountDao
    {
        String myConnstr = new MySQLConnectionString("localhost", "test", "root", "root").AsString;
        
        public List<MySQLAccount> GetAccountName(List<string> userIdList)
        {
            
            List<MySQLAccount>  accountList = new List<MySQLAccount>();
            MySQLConnection myConn = new MySQLConnection(myConnstr);
            try
            {
                myConn.Open();
                for (int i = 0; i < userIdList.Count; ++i)
                {
                    MySQLCommand mscom = new MySQLCommand("set names gb2312", myConn);
                   mscom.ExecuteNonQuery();
                    string selectstr = "select * from account where User_ID = '" + userIdList[i] + "'";
                    MySQLCommand myCom = new MySQLCommand(selectstr, myConn);
                    System.Data.Common.DbDataReader reader = myCom.ExecuteReader();
                    
                    try
                    {
                        while (reader.Read())
                        {
                            if (reader.HasRows)
                            {
                                MySQLAccount account = new MySQLAccount();
                                account.userId = reader.GetString(0);
                                account.userName = reader.GetString(1);
                                
                                accountList.Add(account);
                            }
                        }
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.Message);
                    }
                    reader.Close();
                }
                
                myConn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(myConn);
            }
            return accountList;
        }
        public void InsertAccount(List<MySQLAccount> accountList)
        {
            
 
            MySQLConnection myConn = new MySQLConnection(myConnstr);
            try
            {
                myConn.Open();
                for (int i = 0; i < accountList.Count; ++i)
                {
                    string insertstr = "insert into account values('"+ accountList[i].userId + "', '"+accountList[i].userName+"')";
                    MySQLCommand myCom = new MySQLCommand(insertstr, myConn);
                    myCom.ExecuteNonQuery();
                }

                myConn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(myConn);
            }
        }
        public void DeleteAccount(List<string> userIDList)
        {

            MySQLConnection myConn = new MySQLConnection(myConnstr);
            try
            {
                myConn.Open();
                for (int i = 0; i < userIDList.Count; ++i)
                {
                    string deletestr = "delete from account where User_ID = '" + userIDList[i] + "'";
                    MySQLCommand myCom = new MySQLCommand(deletestr, myConn);
                    myCom.ExecuteNonQuery();
                }

                myConn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(myConn);
            }
        }
        public void UpdateAccount(List<MySQLAccount> accountList)
        {
            MySQLConnection myConn = new MySQLConnection(myConnstr);
            try
            {
                myConn.Open();
                for (int i = 0; i < accountList.Count; ++i)
                {
                    string updatestr = "update account set User_Name = '" + accountList[i].userName + "' where User_ID = '"+accountList[i].userId+"'";
                    MySQLCommand myCom = new MySQLCommand(updatestr, myConn);
                    myCom.ExecuteNonQuery();
                }

                myConn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(myConn);
            }
        }
    }
}
